package com.oceansoft.mobile.econsole.exam.util;

/**
 * Created by liujie on 2016/8/29.
 */

import com.oceansoft.mobile.econsole.exam.entity.Option;
import com.oceansoft.mobile.econsole.exam.entity.Questions;
import com.oceansoft.mobile.econsole.exam.persistence.IExamMapper;
import com.oceansoft.mobile.econsole.task.sms.StringUtil;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class QuestionToDb {
    static private IExamMapper examMapper;

    public QuestionToDb(IExamMapper mapper) {
        examMapper = mapper;
    }

    // 全局配置
    HashMap<String, Object> configure = new HashMap<String, Object>();

    Map<String, String> roleMap = new HashMap<String, String>();

    public Map<String, String> getRoleMap() {
        return roleMap;
    }

    public void setRoleMap(Map<String, String> roleMap) {
        this.roleMap = roleMap;
    }

    public String importXLS(String configfile,String deptid,String c, String filePath) throws Exception{
        loadConfig(configfile, null);
        String message = save2DB(filePath,deptid,c);
        return message;
    }
    public String importXLS(String configfile,MultipartFile requestFile,int examid) throws Exception{
        loadConfig(configfile, null);
        String message = saveStream2DB(requestFile,examid);
        return message;
    }

    public void loadConfig(String configName, Map extraMap) {
        String configPath = ConfigFileLoadHelper.getConfigPath(configName);
        ConfigFileLoadHelper.loadCofingFile(configPath, configure, extraMap);



    }

    private String save2DB(String filePath,String deptid,String c) throws Exception {
        System.out.println("=====ehr==save2DB=1");
        Iterator<Row> rowit = null;
        if (StringUtil.isNotNull(filePath)) {
            String fileExtName = filePath.split("[.]")[filePath.split("[.]").length - 1];
            try {
                if ("xls".equalsIgnoreCase(fileExtName)) {
                    // 2007之前格式
                    HSSFWorkbook book = new HSSFWorkbook(new FileInputStream(new File(filePath)));
                    HSSFSheet sheet = book.getSheetAt(0);
                    rowit = sheet.iterator();
                } else if ("xlsx".equalsIgnoreCase(fileExtName)) {
                    XSSFWorkbook book = new XSSFWorkbook(new FileInputStream(new File(filePath)));
                    XSSFSheet sheet = book.getSheetAt(0);
                    rowit = sheet.iterator();
                }

                if (rowit != null) {

                    List fieldNameList = (List) configure.get("fieldNameList");
                    fieldNameList.add("deptId");
                    fieldNameList.add("orderNum");
                    fieldNameList.add("dbId");
                    fieldNameList.remove("role");
//                    parseRow(configure, rowit,deptid,c);

                    // GetXLSHelper.transferXLSData2DB(configure, rowit);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        } else {
//            throw new HuiRuntimeException("--", "Data file is not exist!");
        }
        String message = getResMessage(configure);
        return message;
    }

    private String saveStream2DB(MultipartFile requestFile,int examid) throws Exception {
        Iterator<Row> rowit = null;
        String fileName = requestFile.getOriginalFilename();
        String fileExtName = fileName.split("[.]")[fileName.split("[.]").length - 1];
        try {
            if ("xls".equalsIgnoreCase(fileExtName)) {
                // 2007之前格式
                HSSFWorkbook book = new HSSFWorkbook(requestFile.getInputStream());
                HSSFSheet sheet = book.getSheetAt(0);
                rowit = sheet.iterator();
            } else if ("xlsx".equalsIgnoreCase(fileExtName)) {
                XSSFWorkbook book = new XSSFWorkbook(requestFile.getInputStream());
                XSSFSheet sheet = book.getSheetAt(0);
                rowit = sheet.iterator();
            }

            if (rowit != null) {

                List fieldNameList = (List) configure.get("fieldNameList");
                parseRow(configure, rowit, examid);

            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        String message = getResMessage(configure);
        return message;
    }

    private void parseRow(Map configure, Iterator<Row> rowit,int examid) throws Exception{
        int uploadRows = 0;
        int insertRows = 0;
        int updateRows = 0;
        int excepRows = 0;
        System.out.println("=====ehr==parseRow=1");
        int beginrow = Integer.parseInt((String) configure.get("begin"));
        String end = configure.get("end") == null || ((String) configure.get("end")).indexOf(":") == -1 ? ":noEndSign"
                : (String) configure.get("end");
        String endKey = end.split(":")[0];
        String endValue = end.split(":")[1];
        String errorRows = "";

        // String ignoreRows = (String) configure.get("ignores");
        boolean isEnd = false;
        while (rowit.hasNext()) {
            Row row = rowit.next();
            String rowContent = "";
            Map rowdata = new HashMap();
            if (row != null) {

                int rowNum = row.getRowNum();
                //按照第一行的label进行设定
                if(rowNum == 0)
                {
                    Iterator<Cell> it = row.iterator();
                    while (it.hasNext()) {
                        Cell cell = it.next();
                        if (cell == null) {
                            continue;
                        }
                        Object cellContent = "";
                        int colNum = cell.getColumnIndex();
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            cellContent = cell.getNumericCellValue();
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                cellContent = cell.getDateCellValue();
                            }
                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            cellContent = cell.getStringCellValue();
                        } else {
                            cellContent = cell.getDateCellValue();
                        }

                        if(StringUtil.isNotNull(cellContent.toString()))
                        {
                            Map colMap = (Map) configure.get("fieldMapping");
                            Map map = (Map) colMap.get(colNum + "");
                            String fieldName = "";
                            if (map == null) {
                                Iterator<String>  keyit = colMap.keySet().iterator();
                                while(keyit.hasNext())
                                {
                                    String k = keyit.next();
                                    Map m = (Map)colMap.get(k);
                                    if(null==m){
                                        continue;
                                    }
                                    String l = (String)m.get("labelName");
                                    if(cellContent.equals(l))
                                    {
                                        //把这一行设置为实际的字段
                                        colMap.put(colNum + "", m);
                                        m.put("isnew", "1");
                                        colMap.put(k, map);
                                        //map.put("fieldName", fieldName);
                                        break;
                                    }
                                }
                            } else {
                                //拿到当前列的字段配置
                                fieldName = (String) map.get("fieldName");
                                String labelName = (String) map.get("labelName");
                                //比对是否一致
                                if(cellContent.equals(labelName))
                                {
                                    map.put("isnew", "1");
                                }
                                else
                                {
                                    //查找配置文件中配置的列

                                    Iterator<String>  keyit = colMap.keySet().iterator();
                                    while(keyit.hasNext())
                                    {
                                        String k = keyit.next();
                                        Map m = (Map)colMap.get(k);
                                        String l = (String)m.get("labelName");
                                        if(cellContent.equals(l))
                                        {
                                            //把这一行设置为实际的字段
                                            colMap.put(colNum + "", m);
                                            m.put("isnew", "1");
                                            colMap.put(k, map);
                                            //map.put("fieldName", fieldName);
                                            break;
                                        }
                                    }
                                }
                            }
                        }
                        else
                        {
                            continue;
                        }



                    }
                    List<String> removeK = new ArrayList<String>();
                    //将没有匹配到的全部去除
                    Map colMap = (Map) configure.get("fieldMapping");
                    Iterator<String>  keyit = colMap.keySet().iterator();
                    while(keyit.hasNext())
                    {
                        String k = keyit.next();
                        Map m = (Map)colMap.get(k);
                        if(null==m||null==m.get("isnew"))
                        {
                            //colMap.remove(m);
                            removeK.add(k);
                        }
                    }
                    for (String string : removeK) {
                        colMap.remove(string);
                    }

                    //Map colMap = (Map) configure.get("fieldMapping");

                    if(colMap.isEmpty())// 去掉此处代码(qiur)  ||!colMap.containsKey("code")
                    {
                        configure.put("errorRows", "上传文件内容不正确!");
                        return;
                    }


                    System.out.println("=====ehr==parseRow=2"+colMap.toString());
                }

                if (rowNum < beginrow) {
                    continue;
                }
                uploadRows++;
                Iterator<Cell> it = row.iterator();

                try {



                    while (it.hasNext()) {

                        Object cellContent = "";
                        Cell cell = it.next();
                        if (cell == null) {
                            continue;
                        }
                        int colNum = cell.getColumnIndex();
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            cellContent = cell.getNumericCellValue();
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                cellContent = cell.getDateCellValue();
                            }
                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            cellContent = cell.getStringCellValue();
                        } else {
                            cellContent = cell.getDateCellValue();
                        }
                        if (endKey.equals(colNum + "")) {
                            if (cellContent.equals(endValue)) {
                                isEnd = true;
                                break;
                            }
                        }
                        cellContent = cell2DB(configure, colNum, cellContent, rowdata);

                        //
                        Map map = (Map) ((Map) configure.get("fieldMapping")).get(colNum + "");
                        String fieldName = "";
                        if (map == null) {

                        } else {
                            fieldName = (String) map.get("fieldName");

                        }

                        if (cellContent != null) {
                            rowContent += cellContent;
                        }

                    }
                } catch (Exception e) {
                    // TODO记录第几行数据解析时出错
                    e.printStackTrace();
                    errorRows += (rowNum + 1) + ",";
                    excepRows++;
                    continue;
                }

                if (!rowContent.equals("")) {
                    // TODO读取到一条数据就往数据库插一条
                    try {

                        int res = 0;
                        insertRows +=updateDB("insert",rowdata,examid);

                    } catch (Exception e) {
                        // TODO记录第几行数据解析时出错
                        e.printStackTrace();
                        errorRows += (rowNum + 1) + ",";
                        excepRows++;
                        throw e;

                        //	continue;
                    }
                } else {
                    break;
                }
            }

            // 读取到结束标记 不再读取
            if (isEnd) {
                break;
            }

        }




        if (errorRows.length() > 1) {
            errorRows = errorRows.substring(0, errorRows.length() - 1);
        }
        configure.put("errorRows", errorRows);
        configure.put("uploadRows", uploadRows);
        configure.put("insertRows", insertRows);
        configure.put("updateRows", updateRows);
        configure.put("excepRows", excepRows);
    }


    private String jp="";
    private int updateDB(String importMode, Map row,int examid)throws Exception {
        int res = 0;
//        int no = (int)Double.parseDouble((String)row.get("no"));
        String type = (String)row.get("type");
        String title = (String)row.get("title");
        String option_1 = (String)row.get("option_1");
        String option_2 = (String)row.get("option_2");
        String option_3 = (String)row.get("option_3");
        String option_4 = (String)row.get("option_4");
        String option_5 = (String)row.get("option_5");
        String answer = (String)row.get("answer");
        int goals = (int)Double.parseDouble((String)row.get("goals"));

        Map<String,String> typeMap= new HashMap<String,String>();
        typeMap.put("单选题","1");
        typeMap.put("多选题","2");
        typeMap.put("判断题","3");

        Questions questions = new Questions();
        questions.setType(typeMap.get(type));
        questions.setTitle(title);
        if("对".equals(answer)) {
            questions.setAnswer("A");
        }else if("错".equals(answer)){
            questions.setAnswer("B");
        }else{
            if(StringUtil.isNotNullAndBlank(answer)&&answer.length()>1) {
                String answerstr = "";
                for(int i=0;i<answer.length();i++){
                    if(i<answer.length()-1) {
                        answerstr += answer.charAt(i) + ",";
                    }else{
                        answerstr += answer.charAt(i);
                    }
                }
                questions.setAnswer(answerstr);
            }else {
                questions.setAnswer(answer);
            }
        }
        questions.setGoals(goals);
        questions.setBelong(examid);
        res = examMapper.saveQuestions(questions);

        Option option = new Option();
        option.setLabel("A");
        if(StringUtil.isNotNullAndBlank(option_1)&&!"NULL".equalsIgnoreCase(option_1)) {
            option.setName(option_1);
        }else{
            option.setName("对");
        }
        option.setQuestionid(questions.getId());
        res = examMapper.saveOption(option);

        option = new Option();
        option.setLabel("B");
        if(StringUtil.isNotNullAndBlank(option_2)&&!"NULL".equalsIgnoreCase(option_2)) {
            option.setName(option_2);
        }else{
            option.setName("错");
        }
        option.setQuestionid(questions.getId());
        res = examMapper.saveOption(option);


        if(StringUtil.isNotNullAndBlank(option_3)&&!"NULL".equalsIgnoreCase(option_3)) {
            option = new Option();
            option.setLabel("C");
            option.setName(option_3);
            option.setQuestionid(questions.getId());
            res = examMapper.saveOption(option);
        }
        if(StringUtil.isNotNullAndBlank(option_4)&&!"NULL".equalsIgnoreCase(option_4)) {
            option = new Option();
            option.setLabel("D");
            option.setName(option_4);
            option.setQuestionid(questions.getId());
            res = examMapper.saveOption(option);
        }
        if(StringUtil.isNotNullAndBlank(option_5)&&!"NULL".equalsIgnoreCase(option_5)) {
            option = new Option();
            option.setLabel("E");
            option.setName(option_5);
            option.setQuestionid(questions.getId());
            res = examMapper.saveOption(option);
        }
        return res;
    }

    @SuppressWarnings("unchecked")
    public static Object cell2DB(Map configure, int colIndex, Object o, Map rowData) {
        Map map = (Map) ((Map) configure.get("fieldMapping")).get(colIndex + "");
        String cellContent = "";
        String fieldName = "";
        if (map == null || o == null) {
            return null;
        } else {

            // 处理各种类型转换
            cellContent = Object2Str(o);

            fieldName = (String) map.get("fieldName");
            if (map.get("prefix") != null) {
                String prefix = (String) map.get("prefix");

                if (StringUtil.isNotNull(prefix)) {
                    if (StringUtil.isNotNull(cellContent)) {
                        if (cellContent.endsWith(prefix)) {
                            cellContent = cellContent.substring(0, cellContent.length() - prefix.length());
                        }
                    }
                }
            }
            if (map.get("suffix") != null) {
                String suffix = (String) map.get("suffix");
                if (StringUtil.isNotNull(suffix)) {
                    if (StringUtil.isNotNull((String) cellContent)) {
                        String c = (String) cellContent;
                        if (c.endsWith(suffix)) {
                            cellContent = c.substring(0, c.length() - suffix.length());
                        }
                    }
                }
            }

            HashMap valueMapping = (HashMap) map.get("valueMapping");
            if (valueMapping != null) {
                Set<String> itemKey = valueMapping.keySet();
                Iterator<String> itemIter = itemKey.iterator();
                while (itemIter.hasNext()) {
                    String key = itemIter.next();
                    if (cellContent.equals(key)) {
                        cellContent = (String) valueMapping.get(key);
                    }
                }
            }

            if (map.get("dataType") != null) {
                String dataType = (String) map.get("dataType");
                if ("int".equals(dataType)) {

                    try {
                        if (cellContent.indexOf(".") > -1) {
                            cellContent = cellContent.substring(0, cellContent.indexOf("."));
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                        cellContent = Integer.MIN_VALUE + "";
                    }

                } else if ("date".equals(dataType)) {
                    if (StringUtil.isNotNull((String) cellContent)) {
                        String famtter = (String) map.get("formatter");
                        String dbformatter = (String) map.get("dbformatter");
                        if (StringUtil.isNull(dbformatter)) {
                            dbformatter = "yyyy-MM-dd";
                        }
                        if (StringUtil.isNotNull(famtter)) {
                            SimpleDateFormat sdf = new SimpleDateFormat(famtter);
                            Date d = null;
                            try {
                                d = sdf.parse(cellContent);
                            } catch (ParseException e) {
                                System.out.println(famtter + "    " + cellContent);
                                e.printStackTrace();
                            }
                            sdf = new SimpleDateFormat(famtter);
                            if (d != null) {
                                cellContent = sdf.format(d);
                            }
                        }
                        if (dbformatter.length() < 11) {
                            try {
                                cellContent = cellContent.substring(0, 10);
                            } catch (Exception e) {
                            }
                        }
                        cellContent = "to_date('" + cellContent.toString() + "','" + dbformatter + "')";
                    }
                } else {
                    // cellContent = "'" + cellContent + "'";
                }
            }
            rowData.put(fieldName, cellContent.toString());
        }
        return cellContent;
    }

    private static String Object2Str(Object cellContent) {
        String s = "";
        if (cellContent instanceof Double) {
            s = String.format("%.4f", (Double) cellContent);
            // System.out.println(String.format("%.2f", (Double)cellContent));

            if (s.endsWith(".00")) {
                s = s.substring(0, s.length() - 3);
            }
        }

        if (cellContent instanceof Date) {
            Date date = ((Date) cellContent);
            java.sql.Date d = new java.sql.Date(date.getTime());
            s = d + "";
            if (date.getHours() > 0) {
                s += " " + date.getHours() + ":" + date.getMinutes() + ":" + date.getSeconds();
            }
        }

        if (cellContent instanceof String) {
            s = (String) cellContent;
        }

        return s;
    }

    private String getResMessage(Map resMap) {
        String message = "";
        message = "上传数据:" + resMap.get("uploadRows") + ",成功新增:" + resMap.get("insertRows") +  ",出错数据:" + resMap.get("excepRows") + ",出错行:" + resMap.get("errorRows");
        return message;
    }
}
